import psycopg2
import pymysql

PSCONFIG = {
    "dbname": "orthanc_v2",
    "user": "postgres",
    "password": "111222",
    "host": "127.0.0.1",
    "port": "5432"
}
MySQLCONFIG = {
    "dbname": "db0",
    "user": "root",
    "password": "Zxc123321",
    "host": "127.0.0.1",
    "port": 3306
}


class DatabaseUtils():

    def __init__(self, db_type='postgres'):
        try:
            self.db_type = db_type
            if self.db_type == 'postgres':
                self.conn = psycopg2.connect(
                    database=PSCONFIG["dbname"],
                    user=PSCONFIG["user"],
                    password=PSCONFIG["password"],
                    host=PSCONFIG["host"],
                    port=PSCONFIG["port"]
                )
                self.cursor = self.conn.cursor()
            elif self.db_type == 'mysql':
                self.conn = pymysql.connect(database=MySQLCONFIG["dbname"],
                                            user=MySQLCONFIG["user"],
                                            password=MySQLCONFIG["password"],
                                            host=MySQLCONFIG["host"],
                                            port=MySQLCONFIG["port"], charset="utf8")
                self.cursor = self.conn.cursor()
            else:
                raise Exception(f' database type:{db_type} is not supported')
        except Exception as e:
            print("failed to connect to postgresql: {0}".format(e))

    def __del__(self):
        print(f"release {self.db_type} connection")
        self.cursor.close()
        self.conn.close()

    def select(self, sql):
        # print("PostgresqlUtils::select: [{0}]".format(sql))
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def upsert(self, sql, args=None):
        # print ("PostgresqlUtils::upsert: [{0}]".format(sql))
        if args:
            self.cursor.execute(sql, args)
        else:
            self.cursor.execute(sql)
        self.conn.commit()

    def delete(self, sql):
        # print ("PostgresqlUtils::delete: [{0}]".format(sql))
        self.cursor.execute(sql)
        self.conn.commit()


postgres = DatabaseUtils('postgres')
mysql = DatabaseUtils('mysql')

if __name__ == "__main__":
    sql = '''
        select * from attachedfiles where filepath like 'original%' limit 100;
    '''
    results = postgres.select(sql)
    print(results)
